package com.antdesignbackend.backend.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface StatMapper{

    @Select(" select d.town__name name, case  when cnt is  null then 0 else cnt end as cnt from cfg_town d left join (\n" +
            "         select count(1) cnt ,b.town_id from  t_enterprise a  left join cfg_town b on a.towncode = b.town_id  where a.types =#{eType} and b.district_id=#{district}  group by b.town_id) e on d.town_id =e.town_id where  d.district_id=#{district} order by d.town_id")
    List<Map<String, String>> entDistributionByDistrict(int eType,int district);

    @Select(" select d.town__name name, case  when cnt is  null then 0 else cnt end as cnt from cfg_town d left join (\n" +
            "         select count(1) cnt ,b.town_id from  t_enterprise a  left join cfg_town b on a.towncode = b.town_id  where a.types =#{eType} and b.town_id=#{town}  group by b.town_id) e on d.town_id =e.town_id where  d.town_id=#{town} order by d.town_id")
    List<Map<String, String>> entDistributionByTown(int eType,int town);

    @Select(" select c.name, case when cnt is null then 0 else cnt end as cnt from (select name,value from sys_dict where type ='AnimalSpecies' ) c left join (\n" +
            "         select a.animal,sum(species+reserve+menstruation+commodity+livestock) cnt from t_enterprise a left join sys_dict b on a.animal=b.name where a.types =1 and  a.districtcode= ${district} and b.type ='AnimalSpecies'  group by a.animal ) d on c.name = d.animal order by c.value")
    List<Map<String, String>> animalNumByDistrict(int district);

    @Select(" select c.name, case when cnt is null then 0 else cnt end as cnt from (select name,value from sys_dict where type ='AnimalSpecies' ) c left join (\n" +
            "         select a.animal,sum(species+reserve+menstruation+commodity+livestock) cnt from t_enterprise a left join sys_dict b on a.animal=b.name where a.types =1 and  a.towncode= ${town}  and b.type ='AnimalSpecies'  group by a.animal ) d on c.name = d.animal order by c.value")
    List<Map<String, String>> animalNumByTown(int town);


    @Select( {"<script>","select animal type,sum(livestocksum)  as cnt,reportmonth time  from t_enterprise a inner join t_monthreportrecord b  on a.enterpriseid = b.eid where animal in <foreach collection='animalList' item='item' open='(' separator=',' close=')'>#{item}</foreach> and reportmonth>#{startDate} and  a.districtcode= ${district}  group by a.animal,b.reportmonth order by reportmonth","</script>"})
    List<Map<String, Object>> stockByDistrict(int district,String startDate,@Param("animalList")List<String> animalList);

    @Select({ "<script>","  select animal type,sum(livestocksum)  as cnt,reportmonth time  from t_enterprise a inner join t_monthreportrecord b  on a.enterpriseid = b.eid where animal in <foreach collection='animalList' item='item' open='(' separator=',' close=')'>#{item}</foreach> and reportmonth>#{startDate} and  a.towncode= ${town}  group by a.animal,b.reportmonth order by reportmonth","</script>"})
    List<Map<String, Object>>  stockByTown(int town, String startDate,@Param("animalList")List<String> animalList);


    @Select( "select count(1)  as cnt,date_format(reportdate, '%Y-%m' ) time  from t_quarantine a inner join t_enterprise b  on a.eid = b.enterpriseid where  reportdate>= #{startDate} and  b.districtcode= ${district}  group by date_format(reportdate, '%Y-%m' ) order by reportdate")
    List<Map<String, Object>> quarantineByDistrict(int district,String startDate);

    @Select( "select count(1)  as cnt,date_format(reportdate, '%Y-%m' ) time  from t_quarantine a inner join t_enterprise b  on a.eid = b.enterpriseid where  reportdate>= #{startDate} and  b.towncode= ${town}  group by date_format(reportdate, '%Y-%m' ) order by reportdate")
    List<Map<String, Object>> quarantineByTown(int town,String startDate);


    @Select( "select COALESCE(SUM(a.number),0)  as cnt,date_format(a.createtime, '%Y-%m' ) time  from t_production a inner join t_enterprise b  on a.eid = b.enterpriseid where  a.createtime>= #{startDate} and situation='死淘' and  b.districtcode= ${district}  group by date_format(a.createtime, '%Y-%m' ) order by a.createtime")
    List<Map<String, Object>> deadByDistrict(int district,String startDate);

    @Select( "select COALESCE(SUM(a.number),0)  as cnt,date_format(a.createtime, '%Y-%m' ) time  from t_production a inner join t_enterprise b  on a.eid = b.enterpriseid where  a.createtime>= #{startDate} and situation='死淘' and  b.towncode= ${town}  group by date_format(a.createtime, '%Y-%m' ) order by a.createtime")
    List<Map<String, Object>> deadByTown(int town,String startDate);

    @Select( " select COALESCE(SUM(a.slaughternumber),0)   as cnt,date_format(a.slaughterdate,  '%Y-%m-%d') time  from t_slaughterrecord a  inner join t_entryrecord c on a.entryRecordId = c.id  inner join t_enterprise b  on c.eid = b.enterpriseid where  a.slaughterdate>=#{startDate} and  b.districtcode= ${district}  group by date_format(a.slaughterdate, '%Y-%m-%d' ) order by a.slaughterdate")
    List<Map<String, Object>> slaughterByDistrict(int district,String startDate);

    @Select( " select COALESCE(SUM(a.slaughternumber),0)   as cnt,date_format(a.slaughterdate,  '%Y-%m-%d') time  from t_slaughterrecord a  inner join t_entryrecord c on a.entryRecordId = c.id  inner join t_enterprise b  on c.eid = b.enterpriseid where  a.slaughterdate>=#{startDate} and  b.towncode= ${town}  group by date_format(a.slaughterdate, '%Y-%m-%d' ) order by a.slaughterdate")
    List<Map<String, Object>> slaughterByTown(int town,String startDate);

    @Select(  {"<script>"," select COALESCE(SUM(a.number),0)  as cnt,date_format(a.createtime, '%Y-%m' ) time,situation type  from t_production a inner join t_enterprise b  on a.eid = b.enterpriseid where  a.createtime>= #{startDate}  and  b.districtcode= ${district} and animaltype= '${animalType}' group by date_format(a.createtime, '%Y-%m' ),situation order by a.createtime","</script>"})
    List<Map<String, Object>> productionByDistrict(@Param("animalType")String animalType,@Param("district")int district,@Param("startDate")String startDate);

    @Select( {"<script>", " select COALESCE(SUM(a.number),0)  as cnt,date_format(a.createtime, '%Y-%m' ) time,situation type  from t_production a inner join t_enterprise b  on a.eid = b.enterpriseid where  a.createtime>= #{startDate}  and  b.towncode= ${town} and animaltype= '${animalType}' group by date_format(a.createtime, '%Y-%m' ),situation order by a.createtime","</script>"})
    List<Map<String, Object>> productionByTown(@Param("animalType")String animalType,@Param("town")int town,@Param("startDate")String startDate);

    @Select(  {"<script>"," select d.town__name name, case  when cnt is  null then 0 else cnt end as cnt from cfg_town d left join (\n" +
            "         select sum(dead)  as cnt,b.towncode from t_harmlessreport a inner join t_enterprise b  on a.eid = b.enterpriseid  and b.animal = '${animalType}' where date_format(a.createtime, '%Y-%m' )=#{startDate} group by b.towncode) e  on d.town_id =e.towncode where  d.district_id=${district}  order by d.town_id","</script>"})
    List<Map<String, Object>> harmlessByDistrict(@Param("animalType")String animalType,@Param("district")int district,@Param("startDate")String startDate);

    @Select( {"<script>", " select d.town__name name, case  when cnt is  null then 0 else cnt end as cnt from cfg_town d left join (select sum(dead)  as cnt,b.towncode from t_harmlessreport a inner join t_enterprise b  on a.eid = b.enterpriseid  and b.animal = '${animalType}' where date_format(a.createtime, '%Y-%m' )=#{startDate} group by b.towncode) e  on d.town_id =e.towncode where  d.town_id=${town}  order by d.town_id","</script>"})
    List<Map<String, Object>> harmlessByTown(@Param("animalType")String animalType,@Param("town")int town,@Param("startDate")String startDate);

    @Select(  {"<script>"," select d.town__name name, case  when cnt is  null then 0 else cnt end as cnt from cfg_town d left join (\n" +
            "         select sum(a.number)  as cnt,b.towncode from t_quarantine a inner join t_enterprise b  on a.eid = b.enterpriseid where date_format(a.reportdate, '%Y-%m' )=#{startDate}  <if test='animalType != \"0\"'> and animal = #{animalType} </if> group by b.towncode) e  on d.town_id =e.towncode where  d.district_id=${district}  order by d.town_id","</script>"})
    List<Map<String, Object>> quarantineBarByDistrict(@Param("animalType")String animalType,@Param("district")int district,@Param("startDate")String startDate);

    @Select( {"<script>", " select d.town__name name, case  when cnt is  null then 0 else cnt end as cnt from cfg_town d left join (\n" +
            "         select sum(a.number)  as cnt,b.towncode from t_quarantine a inner join t_enterprise b  on a.eid = b.enterpriseid where date_format(a.reportdate, '%Y-%m' )=#{startDate}  <if test='animalType != \"0\"'> and animal = #{animalType} </if>  group by b.towncode) e  on d.town_id =e.towncode where  d.town_id=${town}  order by d.town_id","</script>"})
    List<Map<String, Object>> quarantineBarByTown(@Param("animalType")String animalType,@Param("town")int town,@Param("startDate")String startDate);

}
